package org.eclipse.epsilon.emc.rdbms.dbConnection;
//********************************************************************************************************************
//**  //This checks the database connection string.
//********************************************************************************************************************
import java.*;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import org.eclipse.epsilon.emc.rdbms.iModel.DBReflexiveModel;
import org.h2.tools.RunScript;

public class Connection {

   protected java.sql.Connection  con = null;
   protected static  String url;
   protected  static String serverName;    //hostname for the database server
   protected  static String databaseName;  //the databasse name that we want to connect to
   
   // Constructor
   public Connection(String url, String serverName, String databaseName)
   {
  	 this.url=url;
  	 this.serverName=serverName;
  	 this.databaseName=databaseName;
   }
   
   public String geturl()
   {
  	return this.url; 
   }
   
   public String getserverName()
   {
  	return this.serverName; 
   }
   
   
   public String getdatabaseName()
   {
  	return this.databaseName; 
   }

   public void seturl(String url)
   {
   	this.url = url;
   }
   
   public void setserverName(String serverName)
   {
   	this.serverName = serverName;
   }
   
   
   public void setdatabaseName(String databaseName)
   {
   	this.databaseName = databaseName;
   }
   
   
   //get the connection URL for the database connection.
   private String getConnectionUrl()
   {
        return geturl();
   }
   
   
   public void db_setup(String fileLocation)
   {
	   
	   con= this.getConnection();
       if(con!=null)
       {
    	   try 
    	   {
			RunScript.execute(con, new FileReader(fileLocation));
		   } 
    	   catch (FileNotFoundException e) 
    	   {
			// TODO Auto-generated catch block
			e.printStackTrace();
		   } 
    	   catch (SQLException e) 
    	   {
			// TODO Auto-generated catch block
			e.printStackTrace();
		   }	   
       }
   }
   
   
   
   public java.sql.Connection getConnection()
   {
  	//This is the driver for the SQL Server 2012.
        try{
      	  Class.forName("org.h2.Driver");
             con = java.sql.DriverManager.getConnection(getConnectionUrl());     	           
        }
         catch(Exception e)
            {
             e.printStackTrace();
             System.out.println("Error Trace in getConnection() : " + e.getMessage());
           }   	 
        return con;
    }
   
   /*
        Display the driver properties, database details for test purposes only.
   */ 
   public void getDbProperties(){
        java.sql.DatabaseMetaData dm = null;
        java.sql.ResultSet rs = null;
        try{
             con= this.getConnection();
             if(con!=null){
                  dm = con.getMetaData();
                  System.out.println("Driver Information");
                  System.out.println("\tDriver Name: "+ dm.getDriverName());
                  System.out.println("\tDriver Version: "+ dm.getDriverVersion ());
                  System.out.println("\nDatabase Information ");
                  System.out.println("\tDatabase Name: "+ dm.getDatabaseProductName());
                  System.out.println("\tDatabase Version: "+ dm.getDatabaseProductVersion());
                  System.out.println("Avalilable Catalogs ");
                  rs = dm.getCatalogs();
                  while(rs.next()){
                       System.out.println("\tcatalog: "+ rs.getString(1));
                  } 
                  rs.close();
                  rs = null;
                  closeConnection();
             }else System.out.println("Error: There was an issue getting the datbaase property information.");
        }catch(Exception e){
             e.printStackTrace();
        }
        dm=null;
   }     
   
   
  
public ArrayList getSQLTables()
   {
	ArrayList<String> arrTables = new ArrayList(); 
	int i = 0;							   
	
	java.sql.ResultSet rs = null;       
       try
       {
    	    con= this.getConnection();  
            if(con!=null)               
            {
            	String[] type = new String[1];
            	type[0] = "TABLE";      
            	rs = con.getMetaData().getTables(getdatabaseName().toUpperCase(), null, null, type);
   	           while (rs.next())
                 {
   	        	   arrTables.add(rs.getString(3));
   	        	   i++;
                 }
				  return arrTables;
             }
             else System.out.println("No Database Connection was set");
        }
        catch(Exception e)
        {
             e.printStackTrace();
        }
		return null;
  }      
           
  public ArrayList<String> getSQLColumns(String tablename) 
   {
	  ArrayList<String> arrCols = new ArrayList<String>();
  	try
  	{
  		 con= this.getConnection();
           if(con!=null)
           {
        	      
        	      DatabaseMetaData dbmd = con.getMetaData();
        	      ResultSet rs = dbmd.getColumns(getdatabaseName().toUpperCase(), null,tablename,"%");
        	      
        	      while (rs.next()) 
        	      {
        	            arrCols.add(rs.getString("COLUMN_NAME"));
        	      }
        	return arrCols;      
           } 
           else System.out.println("Error: There were problems retrieving the column information");
      }
  	catch(Exception e)
      {
  		System.out.println("Error: There were problems retrieving the column information");
           e.printStackTrace();
	 }
      return arrCols;
   }

	private void closeConnection()
   {
        try{
             if(con!=null)
                  con.close();
             con=null;
        }catch(Exception e){
             e.printStackTrace();
        }
   }
	
	public ArrayList<String> getPrimaryKey(String tablename)
	{
		 
		java.sql.DatabaseMetaData md = null;
	       java.sql.ResultSet rs = null;
	       try
	       {
	            con= this.getConnection();
	            if(con!=null)
	            {
	          	    rs = con.getMetaData().getPrimaryKeys(null, null, tablename);
	          	    ArrayList<String> arrPrimaryKeysName = new ArrayList<String>(); 
	          	    ArrayList<String> arrPrimaryKeysIds = new ArrayList<String>();
	          	    int recordCount = 0;
	          	    
	          	    while (rs.next()) 
	          	   {	
	               String columnName = rs.getString("COLUMN_NAME");
	                arrPrimaryKeysName.add(columnName);
	                recordCount++; 
	              }
	          	    
	          	  if(recordCount==0)
	          	  {
	          		for (int i = 1; i <= getSQLColumns(tablename).size(); i++)
		         	   {
	          			arrPrimaryKeysIds.add(Integer.toString(i));			         	   
		         	   }
		          } 
	          	  else 
	          	  {
	          		  
	          		  
	          		for (int i = 0; i < getSQLColumns(tablename).size() ; i++)
	          		{
	          			  if(arrPrimaryKeysName.contains(getSQLColumns(tablename).get(i).toString()))
	          		      { 
	          		    	arrPrimaryKeysIds.add(Integer.toString(i));			         	   	     
	          		      }
	          		      
		         	  }
	          	  }
	          	  return arrPrimaryKeysIds;  
	            }
	                 else System.out.println("Error: No Connection defined");
	        }
	        catch(Exception e)
	        {
	             e.printStackTrace();
	        }
	        return null;
	  }      
		
	public ArrayList<String[]> getAllRows(String tablename) throws SQLException
	{	
		ArrayList <String[]> result = new ArrayList<String[]>(); 
		Statement st = con.createStatement();
		// Turn use of the cursor on. 
		st.setFetchSize(5000);  //Fetch 5000 rows at a time should see a performance benefit may need to tweak to see if we can improve performance more.
		ResultSet rs = st.executeQuery("SELECT * FROM INFORMATION_SCHEMA.".concat(tablename)); 
		int columnCount = rs.getMetaData().getColumnCount();
		while (rs.next()) 
		{ 
			String[] row = new String[columnCount]; 
		    for (int i=0; i <columnCount ; i++) 
		    { 
		       row[i] = rs.getString(i + 1); 
		    } 
		    result.add(row); 
		} 		
		rs.close(); 

		return result; 
	}
	
	public String getcolumnstring(ArrayList<String> arrCols)
	{
		StringBuilder sb = new StringBuilder(); 
		boolean first = true; 
        /* Iterate the column-names */ 
		for (int i=0; i < arrCols.size(); i++) 
		{ 	
			
        sb.append("[".concat(arrCols.get(i)).concat("]"));
		if(i < arrCols.size()-1)
		{
			sb.append(", "); 
		}
		} 
		
		return sb.toString(); 
	}

 
   
   
   }


